﻿using System.Data.SQLite;
using System.Text;
using System.Xml.Linq;
using System.Collections.Generic;
using System.Linq;

namespace Saturn.AppSQLite
{
    public class DbCheck
    {
        #region 检查数据文件是否存在
        /// <summary>
        /// 检查数据文件是否存在
        /// </summary>
        public static void CheckDbExit()
        {
            // 不存在，创建数据库并创建表
            if (!System.IO.File.Exists(SqliteHelper.DataBaseFile))
            {
                string path = System.IO.Path.GetDirectoryName(System.Reflection.Assembly.GetExecutingAssembly().GetName().CodeBase);

                //if (!System.IO.File.Exists(SqliteHelper.DataBaseFile))
                //{
                //先创建文件夹
                System.IO.Directory.CreateDirectory(path + "\\DB");
                //再创建数据库文件
                SQLiteConnection.CreateFile(SqliteHelper.DataBaseFile);


                XDocument xml = XDocument.Load(path + "\\Config.xml");
                XElement root = xml.Root;

                //创建表
                var sb = new StringBuilder();

                foreach (var item in root.Element("table").Elements("sql"))
                {
                    sb.AppendLine(item.Value + ";");
                }

                var conn = new SQLiteConnection(SqliteHelper.SqlConnection);
                conn.Open();

                var cmd = new SQLiteCommand
                {
                    Connection = conn,
                    CommandText = sb.ToString()
                };
                cmd.Connection = conn;
                int exec = cmd.ExecuteNonQuery();
                conn.Close();

                if (exec != -1)
                {
                    //更新字段NODE的名字和内容。
                    var b = from f in root.Element("table").Elements("sql") select f;
                    foreach (var i in b) { i.Attribute("isuse").SetValue("1"); }
                    xml.Save(path + "\\Config.xml");

                }
                //}
            }
            // 存在 判断表是否存在 (没有判断表中的字段是否更新)
            else
            {
                string path = System.IO.Path.GetDirectoryName(System.Reflection.Assembly.GetExecutingAssembly().GetName().CodeBase);
                string tableName = string.Empty;
                string isuse = "";
                List<string> lstTable = new List<string>();
                XDocument xml = XDocument.Load(path + "\\Config.xml");
                XElement root = xml.Root;

                //创建表
                var sb = new StringBuilder();
                foreach (var item in root.Element("table").Elements("sql"))
                {
                    tableName = item.Attribute("tablename").Value;
                    isuse = item.Attribute("isuse").Value;

                    if (isuse != "1")
                    {
                        lstTable.Add(tableName);
                        sb.AppendLine(item.Value + ";");
                    }
                }

                var conn = new SQLiteConnection(SqliteHelper.SqlConnection);
                conn.Open();

                var cmd = new SQLiteCommand
                {
                    Connection = conn,
                    CommandText = sb.ToString()
                };
                cmd.Connection = conn;
                int exec = cmd.ExecuteNonQuery();

                conn.Close();

                if (exec != -1)
                {
                    //更新XML内容。
                    foreach (var tbName in lstTable)
                    {
                        var b = from f in root.Element("table").Elements("sql") where (string)f.Attribute("tablename") == tbName select f;
                        foreach (var i in b) { i.Attribute("isuse").SetValue("1"); }
                        xml.Save(path + "\\Config.xml");
                    }
                }
            }
        }
        #endregion
    }
}